#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Autor: Neuza Figueira '''

import csv
import sqlite3

''' Class to convert the csv files given by the teacher into an sqlite3 database '''
class Database:
    @staticmethod
    def createDB():
        
        # List with the csv file names. 
        listaCSVFile = ['rebcsv/reb0.csv',
                        'rebcsv/reb1.csv',
                        'rebcsv/reb2.csv',
                        'rebcsv/reb3.csv',
                        'rebcsv/reb4.csv',
                        'rebcsv/reb5.csv',
                        'rebcsv/reb6.csv',
                        'rebcsv/reb7.csv',
                        'rebcsv/reb8.csv',
                        'rebcsv/reb9.csv',
                        'rebcsv/reb10.csv']
        theList = []
        names = []

        connection = sqlite3.connect("Database.db")
        cursor = connection.cursor()

        cursor.execute('''drop table if exists Database''')
        connection.commit()

        cursor.execute('''create table if not exists Database (codigo_docente INTEGER,
                          				       nome_completo_docente TEXT,
                           				       ultimo_grau TEXT,
                           				       curso TEXT,
                           				       categoria TEXT,
                           				       regime_tempo TEXT,
                           				       ano_lectivo TEXT,
                           				       tipo_estabelecimento TEXT,
                           				       nome_estabelecimento TEXT)''')




        for csvFile in listaCSVFile:
            csvReader = csv.reader(open(csvFile, 'rb'), delimiter=',')
            line = 0
          
            for row in csvReader:
                list2 = []
                for column in row:
                    list2.append(str(column).decode('utf-8'))
                if line != 0:
                    cmd = '''insert into Database (codigo_docente,
						  nome_completo_docente,
                           		  	  ultimo_grau,
                           			  curso,
                           		          categoria,
                           			  regime_tempo,
                           			  ano_lectivo,
                           			  tipo_estabelecimento,
                           		          nome_estabelecimento)
				values("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                                format(str(row[0]).replace( '"', '¨' ),
                                       str(row[1]).replace( '"', '♦' ),
                                       str(row[2]).replace( '"', '♦' ),
                                       str(row[3]).replace( '"', '♦' ),
                                       str(row[4]).replace( '"', '♦' ),
                                       str(row[5]).replace( '"', '♦' ),
                                       str(row[6]).replace( '"', '♦' ),
                                       str(row[7]).replace( '"', '♦' ),
                                       str(row[8]).replace( '"', '♦' ))

                    cursor.execute(cmd)
                    pass
                else:
                    names = list2            
                line = 1
                pass
            pass

        connection.commit()
        connection.close()
        #print "Database completed successfully!"
        pass


